﻿<%@ WebHandler Language="C#" Class="ContainerCheckCycleDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;

/*
 * Description: 检验周期统计看板
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.7.19
 */

public class ContainerCheckCycleDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getFactoryData":
                        //柱状图
                        r = getFactoryData(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryChartDetail":
                        //柱状图明细
                        r = getFactoryChartDetail(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportFactoryChartDetail":
                        //柱状图明细导出
                        exportFactoryChartDetail(jObject);
                        break;
                    case "getCheckStockData":
                        //折线图
                        r = getCheckStockData(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCheckStockChartDetail":
                        //折线图明细
                        r = getCheckStockChartDetail(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportCheckStockChartDetail":
                        //超x天待检统计明细导出
                        exportCheckStockChartDetail(jObject);
                        break;
                    case "validateRowData":
                        r = validateRowData(jObject);//校验数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //柱状图、柱状图明细复用查询sql
    string getFactoryDataSql(JObject jObject)
    {
        string sql = @"
SELECT f.factoryname,t.mfgordername,t.familylistnames,t.productname,t.productdesc productdescription,
       t.containername,t.fromworkcentername,t.workflowstepname,t.checker,t.checkdate,t.lossqty,t.passqty,t.reportdate,
       t.checksuspendnotes notes,DECODE(t.specchecktype,1,'普通工序',2,'工序检验',3,'转工检验工序',4,'转工序') spectype,t.status,
       CASE WHEN t.status = 2 THEN ROUND(t.checkdate - t.reportdate,4) ELSE ROUND(SYSDATE - t.reportdate,4) END delaydays,
       t.containersteprecordid,DECODE(t.entrustmeasurement,'1','是','否') entrustmeasurement,rownum rn
FROM r_checkcycleboard t
LEFT JOIN mes_factory_mapping@insitedb74 f ON f.factorycode = t.factorycode
--LEFT JOIN containercurrentinfo@insitedb74 cci ON t.containername = cci.containername
LEFT JOIN entrustmeasurement@insitedb74 en ON en.containerid = t.containerid AND en.workflowstepid = t.workflowstepid
WHERE t.isinstore = 0 AND t.reportdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') AND t.reportdate <= TO_DATE('{1} 23:59:59','yyyy-mm-dd hh24:mi:ss') 
      AND t.factorycode IN ('600','601','602','607','608','609') AND en.entrustmeasurementid IS NULL";

        JArray reportdate = JArray.Parse(jObject["reportdate"].ToString());
        sql = string.Format(sql, reportdate[0].ToString(), reportdate[1].ToString());

        //检验时间
        if (jObject["checkdate"] != null && jObject["checkdate"].ToString() != "")
        {
            JArray selectdate = JArray.Parse(jObject["checkdate"].ToString());
            sql += string.Format(" AND t.checkdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", selectdate[0].ToString());
            sql += string.Format(" AND t.checkdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", selectdate[1].ToString());
        }

        //主制分厂
        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        //主制工区
        if (jObject["workcentername"] != null && jObject["workcentername"].ToString() != "")
        {
            sql += string.Format(" AND t.fromworkcentername = '{0}'", jObject["workcentername"].ToString());
        }

        //订单
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND t.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND t.familylistnames = '{0}'", jObject["productfamilyname"].ToString());
        }

        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND t.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND t.containername = '{0}'", jObject["containername"].ToString());
        }

        //检验员
        if (jObject["checker"] != null && jObject["checker"].ToString() != "")
        {
            sql += string.Format(" AND t.checker = '{0}'", jObject["checker"].ToString());
        }

        if (jObject["seriesName"] != null)
        {
            string seriesName = jObject["seriesName"].ToString();
            if (seriesName.IndexOf("超") > -1)
            {
                if (seriesName.IndexOf("+") > -1)
                {
                    //超xx天检验+超xx天未检
                    string temp = @" AND ((t.status = 2 AND t.checkdate - t.reportdate > {0}) OR (t.status = 1 AND SYSDATE - t.reportdate > {0}))";
                    sql += string.Format(temp, jObject["interval"].ToString());
                }
                else
                {
                    //超xx天未检
                    string temp = @" AND NVL(t.entrustmeasurement,0) = 0 AND t.status = 1 AND SYSDATE - t.reportdate > {0}";
                    sql += string.Format(temp, jObject["interval"].ToString());
                }
            }
            else
            {
                if (seriesName == "已检待处理")
                {
                    string temp = @" AND t.checksuspendnotes IS NOT NULL AND t.status = 2 AND t.checkdate - t.reportdate > {0}";
                    sql += string.Format(temp, jObject["interval"].ToString());
                }
                if (seriesName == "待生产厂处理")
                {
                    string temp = @" AND t.checksuspendnotes IS NOT NULL AND t.status = 1 AND SYSDATE - t.reportdate > {0}";
                    sql += string.Format(temp, jObject["interval"].ToString());
                }
            }
        }

        //检验类型
        if (jObject["spectype"] != null)
        {
            string spectype = jObject["spectype"].ToString();
            if (spectype == "")
            {
                sql += " AND t.specchecktype IN (1,2)";//普通工序
            }
            else if (spectype == "普通工序")
            {
                sql += " AND t.specchecktype = 1";//普通工序
            }
            else if (spectype == "检验工序")
            {
                sql += " AND t.specchecktype = 2";//检验工序
            }
            else
            {
                sql += " AND t.specchecktype = 3";//转工检验工序
            }
        }
        return sql;
    }

    //柱状图
    Result getFactoryData(JObject jObject)
    {
        string sql = @"
SELECT t.factoryname,COUNT(t.rn) total,
       SUM(CASE WHEN t.delaydays > {0} THEN 1 ELSE 0 END) qty,
       SUM(CASE WHEN t.status = 1 AND t.entrustmeasurement = '否' AND t.delaydays > {0} THEN 1 ELSE 0 END) notcheckqty,
       SUM(CASE WHEN t.delaydays > {0} AND t.status = 2 AND t.notes IS NOT NULL THEN 1 ELSE 0 END) dclqty,
       SUM(CASE WHEN t.delaydays > {0} AND t.status = 1 AND t.notes IS NOT NULL THEN 1 ELSE 0 END) nocheckdclqty
FROM ( {1} ) t 
GROUP BY t.factoryname";
        sql = string.Format(sql, jObject["interval"].ToString(), getFactoryDataSql(jObject));
        DataTable dtResult = dal.GetDataTable(sql, "MESDB17");
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //柱状图明细
    Result getFactoryChartDetail(JObject jObject)
    {
        string sql = @"
SELECT cci.workflowstepname currentspec,t.* FROM ({0}) t 
LEFT JOIN containercurrentinfo@insitedb74 cci ON t.containername = cci.containername
";
        sql = string.Format(sql, getFactoryDataSql(jObject));
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"], "MESDB17");
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //折线图、折线图明细复用查询sql
    string getCheckStockDataSql(JObject jObject)
    {
        string sql = @"
SELECT f.factoryname,t.mfgordername,t.familylistnames,t.productname,t.productdesc productdescription,
       t.containername,t.fromworkcentername,t.workflowstepname,
       trunc(t.reportdate) reportdate,
       ROUND(SYSDATE - t.reportdate,4) delaydays,
       DECODE(t.specchecktype,1,'普通工序',2,'检验工序',3,'转工检验工序',4,'转工序') spectype,
       cci.workflowstepname currentspec,
       t.containersteprecordid,
       DECODE(t.entrustmeasurement,'1','是','否') entrustmeasurement,
       rownum rn
FROM r_checkcycleboard t
LEFT JOIN mes_factory_mapping@insitedb74 f ON f.factorycode = t.factorycode
LEFT JOIN containercurrentinfo@insitedb74 cci ON t.containername = cci.containername
LEFT JOIN entrustmeasurement@insitedb74 en ON en.containerid = t.containerid AND en.workflowstepid = t.workflowstepid
WHERE t.reportdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') AND t.reportdate <= TO_DATE('{1} 23:59:59','yyyy-mm-dd hh24:mi:ss')
      AND t.factorycode IN ('600','601','602','607','608','609') AND cci.containerstatus <> 2 AND en.entrustmeasurementid IS NULL AND cci.isinstore = 0";
        JArray reportdate = JArray.Parse(jObject["reportdate"].ToString());
        //报工时间
        sql = string.Format(sql, reportdate[0].ToString(), reportdate[1].ToString());

        //检验时间
        if (jObject["checkdate"] != null && jObject["checkdate"].ToString() != "")
        {
            JArray selectdate = JArray.Parse(jObject["checkdate"].ToString());
            sql += string.Format(" AND t.checkdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", selectdate[0].ToString());
            sql += string.Format(" AND t.checkdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", selectdate[1].ToString());
        }

        //检验间隔天数
        if (jObject["interval"] != null && jObject["interval"].ToString() != "")
        {
            sql += string.Format(" AND ((t.status = 2 AND t.checkdate - t.reportdate > {0}) OR (t.status = 1 AND SYSDATE - t.reportdate > {0}))", jObject["interval"].ToString());
        }

        //主制分厂
        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        //主制工区
        if (jObject["workcentername"] != null && jObject["workcentername"].ToString() != "")
        {
            sql += string.Format(" AND t.fromworkcentername = '{0}'", jObject["workcentername"].ToString());
        }

        //订单
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND t.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND t.familylistnames = '{0}'", jObject["productfamilyname"].ToString());
        }

        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND t.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND t.containername = '{0}'", jObject["containername"].ToString());
        }

        //检验员
        if (jObject["checker"] != null && jObject["checker"].ToString() != "")
        {
            sql += string.Format(" AND t.checker = '{0}'", jObject["checker"].ToString());
        }

        //检验类型
        if (jObject["spectype"] != null)
        {
            string spectype = jObject["spectype"].ToString();
            if (spectype == "")
            {
                sql += " AND t.specchecktype IN (1,2)";//普通工序
            }
            else if (spectype == "普通工序")
            {
                sql += " AND t.specchecktype = 1";//普通工序
            }
            else if (spectype == "检验工序")
            {
                sql += " AND t.specchecktype = 2";//检验工序
            }
            else
            {
                sql += " AND t.specchecktype = 3";//转工检验工序
            }
        }

        if (jObject["plandate"] != null && jObject["plandate"].ToString() != "")
        {
            sql += string.Format(" AND t.reportdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", jObject["plandate"].ToString());
            sql += string.Format(" AND t.reportdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", jObject["plandate"].ToString());
        }
        return sql;
    }

    //折线图数据
    Result getCheckStockData(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT t.reportdate,COUNT(t.rn) qty
FROM ({0}) t
GROUP BY t.reportdate";
        sql = string.Format(sql, getCheckStockDataSql(jObject));
        DataTable dtQueryData = dal.GetDataTable(sql, "MESDB17");

        sql = @"
SELECT to_date('{0}','yyyy-mm-dd') + (ROWNUM - 1) plandate 
FROM dual 
CONNECT BY ROWNUM <= (to_date('{1}','yyyy-mm-dd') - to_date('{2}','yyyy-mm-dd') + 1)";
        if (jObject["reportdate"] != null && jObject["reportdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["reportdate"].ToString());
            sql = string.Format(sql, arr[0].ToString(), arr[1].ToString(), arr[0].ToString());
        }

        DataTable dtResult = dal.GetDataTable(sql, "MESDB17");
        dtResult.Columns.Add("qty");

        foreach (DataRow dr in dtResult.Rows)
        {
            string filter = string.Format("reportdate = '{0}'", Convert.ToDateTime(dr["plandate"]));
            DataRow[] drList = dtQueryData.Select(filter);
            if (drList.Length > 0)
            {
                dr["qty"] = drList[0]["qty"];
            }
            else
            {
                dr["qty"] = 0;
            }

        }

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //折线图明细
    Result getCheckStockChartDetail(JObject jObject)
    {
        string sql = getCheckStockDataSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"], "MESDB17");
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //柱状图明细导出
    void exportFactoryChartDetail(JObject jObject)
    {
        string sql = @"
SELECT cci.workflowstepname currentspec,t.* FROM ({0}) t 
LEFT JOIN containercurrentinfo@insitedb74 cci ON t.containername = cci.containername
";
        sql = string.Format(sql, getFactoryDataSql(jObject));
        DataTable dtResult = dal.GetDataTable(sql, "MESDB17");
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns.Remove("status");
        dtResult.Columns.Remove("rn");
        dtResult.Columns.Remove("delaydays");
        dtResult.Columns.Remove("containersteprecordid");
        dtResult.Columns["factoryname"].Caption = "主制分厂";
        dtResult.Columns["mfgordername"].Caption = "订单号";
        dtResult.Columns["familylistnames"].Caption = "机型";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["fromworkcentername"].Caption = "责任工区";
        dtResult.Columns["workflowstepname"].Caption = "工序";
        dtResult.Columns["checker"].Caption = "检验员";
        dtResult.Columns["checkdate"].Caption = "检验时间";
        dtResult.Columns["lossqty"].Caption = "报废数";
        dtResult.Columns["passqty"].Caption = "合格数";
        dtResult.Columns["reportdate"].Caption = "报工时间";
        dtResult.Columns["currentspec"].Caption = "当前工序";
        if (dtResult.Columns["spectype"] != null)
        {
            dtResult.Columns["spectype"].Caption = "工序类型";
        }
        dtResult.Columns["notes"].Caption = "备注";
        dtResult.Columns["entrustmeasurement"].Caption = "理化或计量";
        ExportHelper.ExportExcel(dtResult, jObject["title"].ToString());
    }

    //删除已撤销报工数据
    Result validateRowData(JObject jObject)
    {
        Result r = new Result();
        int deleteRowCount = 0;
        string sql = @"
SELECT t.containersteprecordid
FROM r_checkcycleboard t
LEFT JOIN containersteprecord@insitedb74 csr ON t.containersteprecordid = csr.containersteprecordid
WHERE csr.containerid IS NULL AND t.containersteprecordid = '{0}'";
        sql = string.Format(sql, jObject["containersteprecordid"].ToString());
        DataTable dt = dal.GetDataTable(sql, "MESDB17");
        if (dt.Rows.Count == 0)
        {
            deleteRowCount = 0;
        }
        else
        {
            sql = @"DELETE r_checkcycleboard t WHERE t.containersteprecordid = '{0}'";
            sql = string.Format(sql, jObject["containersteprecordid"].ToString());
            deleteRowCount = dal.ExecuteSql(sql, "MESDB17");
        }
        r.data = new DataTable();
        r.total = Convert.ToInt32(deleteRowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //折线图明细导出
    void exportCheckStockChartDetail(JObject jObject)
    {
        string sql = getCheckStockDataSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql, "MESDB17");
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns.Remove("rn");
        dtResult.Columns.Remove("containersteprecordid");
        dtResult.Columns["factoryname"].Caption = "主制分厂";
        dtResult.Columns["mfgordername"].Caption = "订单号";
        dtResult.Columns["familylistnames"].Caption = "机型";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["fromworkcentername"].Caption = "责任工区";
        dtResult.Columns["workflowstepname"].Caption = "工序";
        dtResult.Columns["reportdate"].Caption = "报工时间";
        dtResult.Columns["spectype"].Caption = "工序类型";
        dtResult.Columns["delaydays"].Caption = "等待时间";
        dtResult.Columns["currentspec"].Caption = "当前工序";
        dtResult.Columns["entrustmeasurement"].Caption = "理化或计量";
        ExportHelper.ExportExcel(dtResult, jObject["title"].ToString());
    }
}